/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbaccess;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;

/**
 *
 * @author Peter
 */
public class Database {

    private Connection connection;
    private boolean connected;
    public static final String DRIVER_CLASS = "org.apache.derby.jdbc.ClientDriver";
    public static final String URL = "jdbc:derby://localhost/jTaxiSluzba";
    public static final String USER = "jCrons";
    public static final String PASSWORD = "jCrons";
    public static final String QUERY_INSERT_ZAKAZKA = "INSERT INTO ZAKAZKY VALUES(?,?,?,?,?,?,?,?,?,?)";
    public static final String QUERY_INSERT_TAXIKAR = "INSERT INTO TAXIKAR VALUES(?,?,?,?)";
    public static final String QUERY_INSERT_VOZIDLO = "INSERT INTO VOZIDLO VALUES(?,?,?,?,?)";
    public static final String QUERY_INSERT_OSOBNEUDAJE = "INSERT INTO OSOBNEUDAJE VALUES(?,?,?,?,?,?,?)";
    public static final String QUERY_INSERT_MANAGER = "INSERT INTO MANAGER VALUES(?,?)";
    public static final String QUERY_INSERT_DISPECER = "INSERT INTO DISPECER VALUES(?,?)";
    public static final String QUERY_SELECTFROM_ZAKAZKA = "SELECT ZAKAZKY.ID_ZAKAZKY, TAXIKAR.ID_TAXIKARA,START,CIEL,VZDIALENOSTZAKAZKY,CELKOVAVZDIALENOST,CENA from ZAKAZKY join TAXIKAR on ZAKAZKY.ID_TAXIKARA=TAXIKAR.ID_TAXIKARA";
    public static final String QUERY_SELECTFROM_TAXIKAR_FORDISPECER = "SELECT TAXIKAR.ID_TAXIKARA, OSOBNEUDAJE.MENO, OSOBNEUDAJE.PRIEZVISKO, VOZIDLO.ZNACKA, TAXIKAR.STAV from TAXIKAR join OSOBNEUDAJE on TAXIKAR.ID_OSOBNE_UDAJE=OSOBNEUDAJE.ID join VOZIDLO on TAXIKAR.VOZ_SPZ=VOZIDLO.SPZ";
    public static final String QUERY_SELECTFROM_TAXIKAR_FORADMINISTRATOR = "SELECT TAXIKAR.ID_TAXIKARA, MENO, PRIEZVISKO, TELEFON, PLAT, BYDLISKO from TAXIKAR join OSOBNEUDAJE on TAXIKAR.ID_OSOBNE_UDAJE=OSOBNEUDAJE.ID";
    public static final String QUERY_SELECTFROM_TAXIKAR_FORPRIHLASENIE ="SELECT TAXIKAR.ID_TAXIKARA, MENO, PRIEZVISKO FROM TAXIKAR JOIN OSOBNEUDAJE ON TAXIKAR.ID_OSOBNE_UDAJE=OSOBNEUDAJE.ID";
    public static final String QUERY_SELECTFROM_TAXIKAR_FORTAXIKAR = "SELECT TAXIKAR.ID_TAXIKARA, MENO, PRIEZVISKO, DATUMNARODENIA, BYDLISKO, TELEFON, PLAT, ZNACKA, ROKVYROBY, NAJAZDENEKILOMETRE,SPOTREBA, STAV FROM TAXIKAR JOIN OSOBNEUDAJE ON TAXIKAR.ID_OSOBNE_UDAJE=OSOBNEUDAJE.ID JOIN VOZIDLO ON TAXIKAR.VOZ_SPZ=VOZIDLO.SPZ WHERE TAXIKAR.ID_TAXIKARA = ";
    public static final String QUERY_SELECTFROM_VOZIDLO = "SELECT SPZ,ZNACKA,ROKVYROBY,NAJAZDENEKILOMETRE,SPOTREBA FROM VOZIDLO";
    public static final String QUERY_SELECTFROM_OSOBNEUDAJE = "";
    public static final String QUERY_SELECTFROM_MANAGER = "";
    public static final String QUERY_SELECTFROM_DISPECER = "SELECT DISPECER.ID_DISPECERA, MENO, PRIEZVISKO, TELEFON, PLAT, BYDLISKO from DISPECER join OSOBNEUDAJE on DISPECER.OSOBNEUDAJE=OSOBNEUDAJE.ID";
    public static final String QUERY_UPDATETO_VOLNYTAXIKAR = "UPDATE TAXIKAR SET STAV = 'Volny' WHERE ID_TAXIKARA = ";
    public static final String QUERY_UPDATETO_OBSADENYTAXIKAR = "UPDATE TAXIKAR SET STAV = 'Obsadeny' WHERE ID_TAXIKARA = ";
    public static final String QUERY_DELETE_DISPECER="DELETE FROM DISPECER WHERE ID_DISPECERA= ";
    public static final String QUERY_DELETE_TAXIKAR="DELETE FROM TAXIKAR WHERE ID_TAXIKARA= ";
    public static final String QUERY_DELETE_VOZIDLO="DELETE FROM VOZIDLO WHERE SPZ= ";
    public static final String QUERY_DELETE_ZAKAZKA="DELETE FROM ZAKAZKY WHERE ID_ZAKAZKY= ";
    
    public Database() {
        connected = false;
    }

    public void connect() throws DatabaseConnectException {
        if (connected) {
            return;
        }
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            throw new DatabaseConnectException("driver class not found");
        }

        try {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        connected = true;
    }

    public void disconnect() throws DatabaseConnectException {
        try {
            connection.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("error in closing connection");
        }
        connection = null;
        connected = false;
    }

    public void insertIntoZakazka(int id,int taxikarID, Time start, String miestoStartu, Time ciel, String miestoCiela, int vzdialenostZakazky,int celkovaVzdialenost,int cena) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_ZAKAZKA);
            pstm.setInt(1, id);
            pstm.setTime(2, start);
            pstm.setString(3, miestoStartu);
            pstm.setTime(4, ciel);
            pstm.setString(5, miestoCiela);
            pstm.setInt(6, vzdialenostZakazky);
            pstm.setInt(7, celkovaVzdialenost);
            pstm.setInt(8, cena);
            pstm.setInt(9, taxikarID);
            pstm.setString(10, "Vybavena");
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public void insertIntoTaxikar(int id, DBObjOsobneUdaje osobneUdaje,String spz) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_TAXIKAR);
            pstm.setInt(1, id);
            pstm.setInt(2, osobneUdaje.id);
            pstm.setString(3, spz);
            pstm.setString(4, "Volny");
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public void insertIntoVozidlo(DBObjVozidlo v) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_VOZIDLO);
            pstm.setString(1, v.spz);
            pstm.setString(2, v.znacka);
            pstm.setDate(3, v.rokVyroby);
            pstm.setInt(4, v.najazdeneKilometre);
            pstm.setInt(5, v.spotreba);
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public void insertIntoOsobneUdaje(DBObjOsobneUdaje ou) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_OSOBNEUDAJE);
            pstm.setInt(1, ou.id);
            pstm.setString(2, ou.meno);
            pstm.setString(3, ou.priezvisko);
            pstm.setDate(4, ou.datumNarodenia);
            pstm.setString(5, ou.bydlisko);
            pstm.setInt(6, ou.telefon);
            pstm.setInt(7, ou.plat);
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public void insertIntoManager(DBObjManager m) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_MANAGER);
            pstm.setInt(1, m.id);
            pstm.setInt(2, m.osobneUdaje.id);
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public void insertIntoDispecer(DBObjDispecer d) throws DatabaseConnectException {
        PreparedStatement pstm;
        try {
            pstm = connection.prepareStatement(QUERY_INSERT_DISPECER);
            pstm.setInt(1, d.id);
            pstm.setInt(2, d.osobneUdaje.id);
            pstm.execute();
            pstm.close();
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
    }

    public ResultSet selectFromZakazka() throws DatabaseConnectException {
        
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_ZAKAZKA);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }

    public ResultSet selectFromTaxikarForDispecer() throws DatabaseConnectException {
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_TAXIKAR_FORDISPECER);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }
    
    public ResultSet selectFromTaxikarForAdministrator() throws DatabaseConnectException {
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_TAXIKAR_FORADMINISTRATOR);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }
     
    public ResultSet selectFromVozidlo() throws DatabaseConnectException {
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_VOZIDLO);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }
    
    public ResultSet selectFromTaxikarForPrihlasenie() throws DatabaseConnectException{
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_TAXIKAR_FORPRIHLASENIE);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }
    
    public ResultSet selectFromTaxikarForTaxikar(String id) throws DatabaseConnectException{
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_TAXIKAR_FORTAXIKAR+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }

    public DBObjOsobneUdaje selectFromOsobneUdaje() {
        throw new UnsupportedOperationException();
    }

    public DBObjManager selectFromManager() {
        throw new UnsupportedOperationException();
    }

    public ResultSet selectFromDispecer() throws DatabaseConnectException {
        Statement stm;
        ResultSet rs;
        try {
            stm = connection.createStatement();
            rs = stm.executeQuery(QUERY_SELECTFROM_DISPECER);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        return rs;
    }
    
    public void deleteFromTaxikar(String id) throws DatabaseConnectException{
        Statement stm;
        
        try {
            stm = connection.createStatement();
            stm.executeUpdate(QUERY_DELETE_TAXIKAR+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        
                    
    }
    
    public void deleteFromZakazka(String id) throws DatabaseConnectException{
        Statement stm;
        
        try {
            stm = connection.createStatement();
            stm.executeUpdate(QUERY_DELETE_ZAKAZKA+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
        
                    
    }
    
    public void deleteFromDispecer(String id) throws DatabaseConnectException{
        Statement stm;
        
        try {
            stm = connection.createStatement();
            stm.executeUpdate(QUERY_DELETE_DISPECER+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
     
                    
    }
    
    public void updateTaxikarToVolny(String id) throws DatabaseConnectException{
        Statement stm;
        
        try {
            stm = connection.createStatement();
            stm.executeUpdate(QUERY_UPDATETO_VOLNYTAXIKAR+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
     
                    
    }
    
    public void updateTaxikarToObsadeny(String id) throws DatabaseConnectException{
        Statement stm;
        
        try {
            stm = connection.createStatement();
            stm.executeUpdate(QUERY_UPDATETO_OBSADENYTAXIKAR+id);
        } catch (SQLException ex) {
            throw new DatabaseConnectException("database access error");
        }
     
                    
    }
}
